import datetime
import math
import os.path
import random
import re
import threading

import pandas as pd
from openpyxl import load_workbook
from pubsub import pub
from tinydb import TinyDB, Query

from 文档自动填写GUI程序.common import split_string_by_multiple_delimiters
from 文档自动填写GUI程序.sample_data import item_datas

db = TinyDB('db.json')

'''
钻进记录处理
'''


class DrillThread(threading.Thread):
    def __init__(self, path, piler_no, speed=9, interval=4):
        super().__init__()
        self.path = path
        self.piler_no = piler_no
        self.speed = speed
        self.interval = interval

    def run(self) -> None:
        try:
            # 钻进数据填写
            self.get_data()
            # 钻进时间填写
            self.set_time()
        except Exception as e:
            pub.sendMessage('error_status', message=e)

    def get_data(self):
        path = self.path
        no = self.piler_no
        delimiters = [',', ';', '，', '；', '\n']
        piler_no_list = split_string_by_multiple_delimiters(no, delimiters)
        template = "./钻孔桩钻进记录（旋转钻）模板 .xlsx"
        df = pd.read_excel(path, sheet_name="钻孔数据")
        for piler_no in piler_no_list:
            flag = 0
            piler_no = re.sub(r"\s+", "", piler_no)
            if piler_no is None or piler_no == "":
                return
            for index, row in df.iterrows():
                if str(row["桩号"]).lower() == piler_no.lower():
                    height = row["实际桩底标高"]
                    if pd.isna(height):
                        pub.sendMessage('info_status', message='桩基数据错误，请检查该桩号' + row["桩号"] + "的数据")
                    else:
                        write_excel(template, row)
                    flag = 1

            if flag == 0:
                pub.sendMessage('info_status', message='该桩号' + piler_no + "数据不存在!")

    def set_time(self):
        path = self.path
        no = self.piler_no
        interval = self.interval
        speed = self.speed
        delimiters = [',', ';', '，', '；', '\n']
        piler_no_list = split_string_by_multiple_delimiters(no, delimiters)
        df = pd.read_excel(path, sheet_name="开挖时间")
        for piler_no in piler_no_list:
            flag = 0
            piler_no = re.sub(r"\s+", "", piler_no)
            if piler_no is None or piler_no == "":
                return
            for index, row in df.iterrows():
                start_time = row["开挖时间"]
                if pd.notna(start_time):
                    time_obj = datetime.datetime.strptime(str(start_time), "%Y-%m-%d %H:%M:%S")
                    if str(row["桩号"]).lower() == piler_no.lower():
                        if time_obj.hour != 0 and time_obj.minute != 0:
                            handle_time(start_time, piler_no, speed, interval)
                        else:
                            pub.sendMessage('info_status', message='桩基数据错误，请检查该桩号' + row["桩号"] + "的数据")
                        flag = 1
                else:
                    pub.sendMessage('info_status', message='桩基数据错误，请检查该桩号' + row["桩号"] + "的数据")
            if flag == 0:
                pub.sendMessage('info_status', message='该桩号' + piler_no + "数据不存在!")


# 岩层厚度
def stratum(data, sheet):
    moderate = data["中风化岩层厚度"]
    last = moderate % 2
    if last != 0:
        if last > 1:
            sheet["R26"] = round(last, 2)
        else:
            sheet["R26"] = round(last + 1, 2)
    else:
        sheet["R26"] = 2
    if last > 1:
        if 10 < moderate <= 12:
            sheet["R25"] = 2
            sheet["R24"] = 2
            sheet["R23"] = 2
            sheet["R22"] = 2
            sheet["R21"] = 2
        if 12 < moderate <= 14:
            sheet["R25"] = 2
            sheet["R24"] = 3
            sheet["R23"] = 3
            sheet["R22"] = 2
            sheet["R21"] = 2
        if 14 < moderate <= 16:
            sheet["R25"] = 2
            sheet["R24"] = 3
            sheet["R23"] = 3
            sheet["R22"] = 3
            sheet["R21"] = 3
        if 16 < moderate <= 18:
            sheet["R25"] = 3
            sheet["R24"] = 4
            sheet["R23"] = 3
            sheet["R22"] = 3
            sheet["R21"] = 3
        if 18 < moderate <= 20:
            sheet["R25"] = 3
            sheet["R24"] = 4
            sheet["R23"] = 4
            sheet["R22"] = 4
            sheet["R21"] = 3
    else:
        if 10 < moderate <= 12:
            sheet["R25"] = 2
            sheet["R24"] = 2
            sheet["R23"] = 2
            sheet["R22"] = 2
            sheet["R21"] = 1
        if 12 < moderate <= 14:
            sheet["R25"] = 2
            sheet["R24"] = 3
            sheet["R23"] = 2
            sheet["R22"] = 2
            sheet["R21"] = 2
        if 14 < moderate <= 16:
            sheet["R25"] = 2
            sheet["R24"] = 3
            sheet["R23"] = 3
            sheet["R22"] = 3
            sheet["R21"] = 2
        if 16 < moderate <= 18:
            sheet["R25"] = 3
            sheet["R24"] = 3
            sheet["R23"] = 3
            sheet["R22"] = 3
            sheet["R21"] = 3
        if 18 < moderate <= 20:
            sheet["R25"] = 3
            sheet["R24"] = 4
            sheet["R23"] = 4
            sheet["R22"] = 3
            sheet["R21"] = 3
    strong = data["强风化岩层厚度"]
    QMl = data["素填土厚度"]
    sheet["R19"] = round(QMl, 2)
    sheet["R20"] = strong


def inclination(sheet, piler_no):
    inclination_data = {}
    inclination_index = []
    results = db.search(Query().piler_no == piler_no)
    for i in range(0, 8):
        if results:
            num = results[0]["data"][i]
        else:
            num = get_random_integer(0, 24)
            inclination_index.append(num)
        item = item_datas[num]
        sheet["X" + str(19 + i)] = item[0]
        if item[1] != 0:
            sheet["Y" + str(19 + i)] = item[1]
        if item[2] != 0:
            sheet["Z" + str(19 + i)] = item[2]
        if item[3] != 0:
            sheet["AA" + str(19 + i)] = item[3]
        if item[4] != 0:
            sheet["AB" + str(19 + i)] = item[4]
    if len(inclination_index) > 0:
        inclination_data["piler_no"] = piler_no
        inclination_data["data"] = inclination_index
        db.upsert(inclination_data, Query().piler_no == piler_no)


def get_random_integer(start, end):
    return random.choice(list(range(start, end)))


def write_excel(path, data):
    piler_no = data["桩号"]
    wb = load_workbook(path)
    sheet = wb.active
    sheet["V6"] = data["墩台编号"]
    sheet["AF6"] = data["桩号"]
    sheet["F9"] = data["地面标高"]
    sheet["K9"] = data["孔外水位标高"]
    sheet["S9"] = data["护筒顶高度"]
    sheet["AG9"] = data["护筒埋深"]
    sheet["W12"] = data["桩径"]
    sheet["AD12"] = data["设计桩底标高"]
    sheet["V26"] = data["实际桩底标高"]
    sheet["N19"] = 0
    # 中风化岩层厚度
    stratum(data, sheet)

    # 孔斜度
    inclination(sheet, piler_no)

    wb.close()
    file_name = piler_no + ".钻孔桩钻进记录（旋转钻） .xlsx"
    pub.sendMessage('info_status', message=file_name + '    ----->文件生成完毕')
    full_path = os.path.join("./out", file_name)
    wb.close()
    wb.save(full_path)


def handle_time(time_obj, piler_no, speed, interval):
    new_time = time_obj
    file_name = str(piler_no) + ".钻孔桩钻进记录（旋转钻） .xlsx"
    path = os.path.join("./out", file_name)
    wb = load_workbook(path, data_only=True)
    sheet = wb.active
    for i in range(0, 8):
        index = str(19 + i)
        sheet["A" + index] = str(new_time.year) + "-" + str(new_time.month) + "-" + str(new_time.day)
        hight = sheet["R" + index].value
        if hight:
            hight = round(hight, 2)
            if sheet["AC" + index].value == "素填土":
                sheet["D" + index] = new_time.hour
                sheet["E" + index] = new_time.minute
                time_temp = math.ceil(hight / 15 * 60)
                delta = datetime.timedelta(minutes=time_temp)
                new_time = new_time + delta
                sheet["F" + index] = new_time.hour
                sheet["G" + index] = new_time.minute
                sheet["H" + index] = round(time_temp / 60, 2)

            if sheet["AC" + index].value == "强风化":
                sheet["D" + index] = new_time.hour
                sheet["E" + index] = new_time.minute
                time_temp = math.ceil(hight / 10 * 60)
                if time_temp > 15:
                    time_temp += random.randint(-2, 2)
                delta = datetime.timedelta(minutes=time_temp)
                new_time = new_time + delta
                sheet["F" + index] = new_time.hour
                sheet["G" + index] = new_time.minute
                sheet["H" + index] = round(time_temp / 60, 2)

            if sheet["AC" + index].value == "中风化":
                sheet["D" + index] = new_time.hour
                sheet["E" + index] = new_time.minute
                time_temp = math.ceil(hight / speed * 60)
                if time_temp > 15:
                    time_temp += random.randint(-2, 2)
                delta = datetime.timedelta(minutes=time_temp)
                new_time = new_time + delta
                sheet["F" + index] = new_time.hour
                sheet["G" + index] = new_time.minute
                sheet["H" + index] = round(time_temp / 60, 2)
            print(interval)
            if interval == 0:
                delta = datetime.timedelta(minutes=0)
            else:
                delta = datetime.timedelta(minutes=random.randint(interval-2, interval))
            new_time += delta
    set_formula(sheet)
    pub.sendMessage('info_status', message=file_name + '    ----->桩基钻进时间填写完毕')
    pub.sendMessage('info_status', message="该桩结束时间：" + str(new_time))
    wb.close()
    wb.save(path)


# 公式恢复
def set_formula(sheet):
    sheet["AB9"] = "=S9-(AG9+0.2)"

    sheet["N20"] = "=T19"
    sheet["N21"] = "=T20"
    sheet["N22"] = "=T21"
    sheet["N23"] = "=T22"
    sheet["N24"] = "=T23"
    sheet["N25"] = "=T24"
    sheet["N26"] = "=T25"

    sheet["P19"] = "=T19"
    sheet["P20"] = "=T20"
    sheet["P21"] = "=T21"
    sheet["P22"] = "=T22"
    sheet["P23"] = "=T23"
    sheet["P24"] = "=T24"
    sheet["P25"] = "=T25"
    sheet["P26"] = "=T26"

    sheet["R19"] = "=T20-R20"

    sheet["T19"] = "=T20-R20"
    sheet["T20"] = "=T21-R21"
    sheet["T21"] = "=T22-R22"
    sheet["T22"] = "=T23-R23"
    sheet["T23"] = "=T24-R24"
    sheet["T24"] = "=T25-R25"
    sheet["T25"] = "=T26-R26"
    sheet["T26"] = "=S9-V26"

    sheet["V19"] = "=V20+R20"
    sheet["V20"] = "=V21+R21"
    sheet["V21"] = "=V22+R22"
    sheet["V22"] = "=V23+R23"
    sheet["V23"] = "=V24+R24"
    sheet["V24"] = "=V25+R25"
    sheet["V25"] = "=V26+R26"
